package com.yy.base.util.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;

@SuppressWarnings("unchecked")
public class ExcelWriteUtil {
	private Workbook workbook = null;
	private Sheet sheet = null;
	private int rowIndex = 0;
	private Row row = null;
	private CellStyle cellStyle = null;
	
	public void createExcel() {
		workbook = new SXSSFWorkbook(1000);
		sheet = workbook.createSheet();
		createHeadStyle();
	}

	public void create2003Excel(){
		workbook = new HSSFWorkbook();// 创建 一个excel文档对象
		sheet = workbook.createSheet();// 创建一个工作
		createHeadStyle();
	}
	
	private void createHeadStyle() {
		cellStyle = workbook.createCellStyle();
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		cellStyle.setFillPattern((short)1);
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		
		Font font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)9);
		
		cellStyle.setFont(font);
	}
	
	public void createExcel(String[] titles, List<String[]> values) {
		createExcel();
		addRow(titles);
		for (String[] value : values) {
			addRow(value);
		}
	}

	public void create2003Excel(String[] titles, List<String[]> values) {
		create2003Excel();
		addRow(titles);
		for (String[] value : values) {
			addRow(value);
		}
	}
	//可合并单元格
	public void createHead(ExcelHead head, int index) {
		Cell cell = row.createCell(index);
		cell.setCellStyle(cellStyle);
		cell.setCellValue(head.getText());
		
		if (head.getWidth() > 0) {
			sheet.setColumnWidth(index, head.getWidth() * 20);
		}
		if (head.getColSpan() > 1 || head.getRowSpan() > 1) {
			
			//合并单元格
			sheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex + head.getRowSpan() - 2, 
					index, index + head.getColSpan() - 1));
		}
	}
	
	public void createHead(ExcelHead[][] heads) {
		Cell cell = null;
		ExcelHead[] head = null;
		for (int j = 0; j < heads.length; j++) {
			head = heads[j];
			addRow();
			for (int i = 0; i < head.length; i++) {
				cell = row.createCell(i);
				cell.setCellStyle(cellStyle);
				cell.setCellValue(head[i].getText());
				
				if (head[i].getWidth() > 0) {
					sheet.setColumnWidth(i, head[i].getWidth() * 20);
				}
				if (head[i].getColSpan() > 1) {
					//合并单元格
					//sheet.addMergedRegion(new CellRangeAddress());
				}
			}
		}
	}
	
	public void addRow() {
		createRow();
	}
	
	public void createRow() {
		row = sheet.createRow(rowIndex);
		rowIndex ++;
	}
	
	public void addRow(String []values) {
		addRow();
		for (int j = 0; j < values.length; j++) {
			addCell(j, values[j]);
		}
	}
	
	public void addCell(int index, Object value) {
		Cell cell = row.createCell(index);
		if (value != null) {
			if (value instanceof Float) {
				cell.setCellValue((Float)value);
			} else if (value instanceof Double) {
				cell.setCellValue((Double)value);
			} else if (value instanceof Integer) {
				cell.setCellValue((Integer)value);
			} else if (value instanceof BigDecimal) {
				BigDecimal bd = (BigDecimal)value;
				if (bd.longValue() == bd.doubleValue()) {
					cell.setCellValue(bd.longValue());
				} else {
					cell.setCellValue(bd.doubleValue());
				}
			} else {
				cell.setCellValue(value + "");
			}
		}
	}
	
	public void addSheet(String sheetName) {
		sheet = workbook.createSheet(sheetName);
	}
	
	public void addSheet() {
		sheet = workbook.createSheet();
	}
	
	public void writeFile(String filePath) throws Exception {
		FileOutputStream fileoutputstream = new FileOutputStream(filePath);
		workbook.write(fileoutputstream);
		fileoutputstream.close();
	}

	public void writeFileToResponse(HttpServletResponse response,String fileName){
		//设置response头信息
		try {
			fileName = URLEncoder.encode(fileName,"UTF8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		response.reset();
		response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
		response.setHeader("Content-disposition","attachment; filename="+fileName+".xlsx" );
		try {
			OutputStream out=response.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void writeFileToResponse(HttpServletResponse response,String fileName ,boolean is2003Xls){
		//设置response头信息
		try {
			fileName = URLEncoder.encode(fileName,"UTF8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		response.reset();
		response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
		response.setHeader("Content-disposition","attachment; filename="+fileName+ (is2003Xls ? ".xls" : ".xlsx") );
		try {
			OutputStream out=response.getOutputStream();
			workbook.write(out);
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}